package cn.ccccltd.waf.id_generator.config.sql;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;

import org.apache.ibatis.jdbc.SQL;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.Assert;
import org.springframework.util.CollectionUtils;

import cn.ccccltd.waf.id_generator.config.sql.param.Order;
import cn.ccccltd.waf.id_generator.config.sql.param.QueryItem;
import cn.ccccltd.waf.id_generator.config.sql.param.QueryParams;
import cn.ccccltd.waf.id_generator.constants.Constants;
import cn.ccccltd.waf.id_generator.util.NameUtil;

public class SqlProvider {

	/**序列号*/
	private static final String UID = "serialVersionUID";
	
	/**数据库表前缀*/
	public static final String TABLE_PREFIX = "waf_id_generator_";
	
	private static final Logger log = LoggerFactory.getLogger(SqlProvider.class);

	
	/**
	 * 功能: 查询条件<br>
	 * 作者: yangjingjiang <br>
	 * 创建日期:2018年3月30日 <br>
	 * 修改者: mender <br>
	 * 修改日期: modifydate <br>
	 * @param sql
	 * @param queryParams
	 */
	public static void processQueryItems(SQL sql, final QueryParams queryParams) {
		
//    	查询条件
		List<QueryItem> queryItems = queryParams.getQueryItems();
		
//		两个条件之间的关系
		String logic = "";
		
		if(!CollectionUtils.isEmpty(queryItems)) {
	
			for (int i = 0; i < queryItems.size(); i++) {
				String colwhere = queryItems.get(i).getColwhere();
				logic = queryItems.get(i).getLogic();
				String relation = queryItems.get(i).getRelation();
				String value = queryItems.get(i).getValue();
				
				Assert.hasText(value,"查询条件的值不能为空！");
				
				// sql注入： 单引号转义
				value = value.replace("'", "''");
				
				//操作符大小写
				Assert.hasText(relation,"relation操作符不能为空！");
				relation = relation.toLowerCase();
				//操作的替换
				if (Constants.RELATION_LIKE.equals(relation) || 
						Constants.RELATION_LLIKE.equals(relation) || 
							Constants.RELATION_RLIKE.equals(relation)) {
					if(Constants.RELATION_LIKE.equals(relation)) {
						value = "%" + value.trim() + "%";
					} else if (Constants.RELATION_RLIKE.equals(relation)) {
						value = "%" + value.trim();
					} else if (Constants.RELATION_LLIKE.equals(relation)) {
						value = value.trim() + "%";
					}
					relation = Constants.RELATION_LIKE;
				} else {
				    if(Constants.RELATION_EQ.equals(relation)) {
				    	relation = " = ";
				    } else if (Constants.RELATION_GT.equals(relation)) {
				    	relation = " > ";
					} else if (Constants.RELATION_GE.equals(relation)) {
						relation = " >= ";
					}else if (Constants.RELATION_LT.equals(relation)) {
						relation = " < ";
					}else if (Constants.RELATION_LE.equals(relation)) {
						relation = " <= ";
					}
				}
				
				StringBuilder queryBuffer = new StringBuilder();
				
				queryBuffer.append(colwhere);
				
				//连接符和值之间添加空格
				queryBuffer.append(" ").append(relation).append(" '" + value + "' ");
				
				String where = queryBuffer.toString();
				
				sql.WHERE(where);
				
				log.info(where);
				
				if (Constants.LOGIC_OR.equalsIgnoreCase(logic.trim())) {
					sql.OR();
				}
				
			}

		}
		
    }
	
	/**
	 * 功能: 排序<br>
	 * 作者: yangjingjiang <br>
	 * 创建日期:2018年3月30日 <br>
	 * 修改者: mender <br>
	 * 修改日期: modifydate <br>
	 * @param sql
	 * @param queryParams
	 */
	public static void processOrderBy(SQL sql, final QueryParams queryParams) {
		
		List<Order> orders = queryParams.getOrders();
		
		if(!CollectionUtils.isEmpty(orders)) {
			
			for (Order order : orders) {
				
				String orderSql = new StringBuilder().append(order.getSortName())
						.append(" ").append(order.getSortType()).toString();
				
				sql.ORDER_BY(orderSql);
				
				log.info(orderSql);
				
			}
			
		}
    }
	
	/**
	 * 功能: 生成where+order语句<br>
	 * 作者: yangjingjiang <br>
	 * 创建日期:2018年3月30日 <br>
	 * 修改者: mender <br>
	 * 修改日期: modifydate <br>
	 * @param sql
	 * @param queryParams
	 */
	public static void processSelectByPageSql(SQL sql, Class<?> cls, final QueryParams queryParams) {
		
		sql.SELECT("*");
		
		sql.FROM(getTableName(cls));
		
		processQueryItems(sql, queryParams);
		
		processOrderBy(sql, queryParams);
		
	}
	
	
	/**
	 * 功能: 生成insert语句,如果值为空则插入空值<br>
	 * 作者: yangjingjiang <br>
	 * 创建日期:2018年3月30日 <br>
	 * 修改者: mender <br>
	 * 修改日期: modifydate <br>
	 * @param sql
	 * @param bean
	 */
	public static void processInsertSql(SQL sql, Object bean) {

		Class<?> cls = bean.getClass();
		
		sql.INSERT_INTO(getTableName(cls));
		
		while (null != cls) {
			
			List<Field> declaredFields = getFields(cls);
			
			declaredFields.forEach(field -> {

				field.setAccessible(true);
					
				String name = field.getName();
				
				sql.VALUES(NameUtil.getUnderLineName(name), NameUtil.getMybatisValue(name));
				
			});
			
			cls = cls.getSuperclass();
			
		}
			
	}
	
	public static void processDeleteSql(SQL sql, Object bean) {
		
		Class<?> cls = bean.getClass();
		
		sql.DELETE_FROM(getTableName(cls));
		
		while (null != cls) {
			
			List<Field> declaredFields = getFields(cls);
			
			declaredFields.forEach(field -> {
				
				field.setAccessible(true);
				
				Object val = null;
				
				try {
					
					val = field.get(bean);
					
				} catch (IllegalAccessException e) {
					
					e.printStackTrace();
					
					log.error("获取属性值报错！", e);
					
				}
				
				if (val != null) {
					
					String name = field.getName();
					
					sql.WHERE(NameUtil.getUnderLineName(name) + "=" + NameUtil.getMybatisValue(name));
					
				}
				
			});
			
			cls = cls.getSuperclass();
			
		}
		
	}
	
	public static void processGetSql(SQL sql, Object bean) {

		Class<?> cls = bean.getClass();
		
		sql.SELECT("*");
		
		sql.FROM(getTableName(cls));
		
		while (null != cls) {
			
			List<Field> declaredFields = getFields(cls);
			
			declaredFields.forEach(field -> {

				field.setAccessible(true);
					
				Object val = null;
				
				try {
					
					val = field.get(bean);
					
				} catch (IllegalAccessException e) {
					
					e.printStackTrace();
					
					log.error("获取属性值报错！", e);
					
				}
				
				if (val != null) {
					
					String name = field.getName();

					sql.WHERE(NameUtil.getUnderLineName(name) + "=" + NameUtil.getMybatisValue(name));
					
				}
				
			});
			
			cls = cls.getSuperclass();
			
		}
	
	}
	
	public static void processUpdateSql(SQL sql, Object bean) {

		Class<?> cls = bean.getClass();
		
		sql.UPDATE(getTableName(cls));
		
		while (null != cls) {
			
			List<Field> declaredFields = getFields(cls);
			
			declaredFields.forEach(field -> {

				field.setAccessible(true);
					
				Object val = null;
				
				try {
					
					val = field.get(bean);
					
				} catch (IllegalAccessException e) {
					
					e.printStackTrace();
					
					log.error("获取属性值报错！", e);
					
				}
				
				if (val != null) {
					
					String name = field.getName();
					
					if (field.isAnnotationPresent(Key.class)) {

						sql.WHERE(NameUtil.getUnderLineName(name) + "=" + NameUtil.getMybatisValue(name));
					
					} else {
						
						sql.SET(NameUtil.getUnderLineName(name) + "=" + NameUtil.getMybatisValue(name));
						
					}
					
				}
				
			});
			
			cls = cls.getSuperclass();
			
		}
			
	}
	
	/**
	 * 功能: 获取所有的属性<br>
	 * 作者: yangjingjiang <br>
	 * 创建日期:2018年3月30日 <br>
	 * 修改者: mender <br>
	 * 修改日期: modifydate <br>
	 * @param cls
	 * @return
	 */
	public static List<Field> getFields(Class<?> cls) {
		
		List<Field> allField = new ArrayList<>();
		
		Field[] declaredFields = cls.getDeclaredFields();
		
		for (int i = 0 ,length = declaredFields.length; i < length; i++) {
			
			Field field = declaredFields[i];
			
			if (UID.equals(field.getName())) {
				
				continue;
				
			}
			
			allField.add(field);
			
		}
		
		return allField;
	}
	
	/**
	 * 功能: 获取表名称<br>
	 * 作者: yangjingjiang <br>
	 * 创建日期:2018年4月2日 <br>
	 * 修改者: mender <br>
	 * 修改日期: modifydate <br>
	 * @param cls
	 * @return
	 */
	public static String getTableName(Class<?> cls) {
		
		return TABLE_PREFIX + NameUtil.getUnderLineName(cls.getSimpleName());
		
	}
}
